﻿Public Class clsGtNgDetails
    Public Function getGtNgDetailsList(b_gt_sys_id As String) As DataTable
        Dim szSql As String = ""
        szSql &= " select * from sf.b_gt_ng_details "
        szSql &= "where b_gt_sys_id = ? and validity = ? order by procedure_no"
        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, b_gt_sys_id, "1")
        If IsNothing(dt) Then
            getGtNgDetailsList = New DataTable
        Else
            getGtNgDetailsList = dt
        End If
    End Function

    Public Function save(en As entity_gt_ng_details) As Integer
        Dim sql As String = ""
        Dim rtn As Integer = 0
        DBacs.BeginTransaction()
        Try

            sql &= "INSERT INTO sf.b_gt_ng_details(procedure_No, ng_num, ng_reason, operator, validity, b_gt_sys_id, update_time) "
            sql &= "VALUES ( ?, ?, ?, ?, ?, ?, (SELECT CURRENT_TIMESTAMP(6)))"
            With en
                rtn = DBacs.ExecuteUpdateSql(sql, -1, .procedure_No, .ng_num, .ng_reason, .oper, .validity, .b_gt_sys_id)
            End With

            Dim integer_procedure_No As Integer = Integer.Parse(en.procedure_No)

            sql = ""
            sql &= " select * from sf.b_gt_ng_procedure where b_gt_sys_id = ? and procedure_no = ? "
            Dim dt00 As DataTable = DBacs.ExecuteSql(sql, -1, en.b_gt_sys_id, en.procedure_No)

            sql = ""
            If dt00.Rows.Count = 1 Then
                sql &= "UPDATE sf.b_gt_ng_procedure"
                sql &= " SET all_ng_num = all_ng_num + ?, update_time = (SELECT CURRENT_TIMESTAMP(6)) "
                sql &= " WHERE sys_id = ? "
                rtn = DBacs.ExecuteUpdateSql(sql, -1, en.ng_num, dt00.Rows(0)("sys_id"))
            Else
                sql &= "INSERT INTO sf.b_gt_ng_procedure(procedure_no,all_ng_num,b_gt_sys_id,update_time) "
                sql &= "VALUES (?,?,?,(SELECT CURRENT_TIMESTAMP(6)))"
                rtn = DBacs.ExecuteUpdateSql(sql, -1, en.procedure_No, en.ng_num, en.b_gt_sys_id)
            End If

            sql = ""
            sql &= " select * from sf.b_gt_ng where b_gt_sys_id = ? "
            Dim dt33 As DataTable = DBacs.ExecuteSql(sql, -1, en.b_gt_sys_id)

            sql = ""
            If dt33.Rows.Count = 1 Then
                sql &= "UPDATE sf.b_gt_ng SET all_ng_num = un_oper_ng_num + oper_ng_num + ?,"
                If integer_procedure_No > 0 Then
                    sql &= " oper_ng_num = oper_ng_num + ?, "
                Else
                    sql &= " un_oper_ng_num = un_oper_ng_num + ?, "
                End If
                sql &= " update_time = (SELECT CURRENT_TIMESTAMP(6)) "
                sql &= " WHERE sys_id = ? "
                rtn = DBacs.ExecuteUpdateSql(sql, -1, en.ng_num, en.ng_num, dt33.Rows(0)("sys_id"))
            Else
                sql &= "INSERT INTO sf.b_gt_ng(all_ng_num, "
                If integer_procedure_No > 0 Then
                    sql &= "oper_ng_num, "
                Else
                    sql &= "un_oper_ng_num, "
                End If
                sql &= "b_gt_sys_id,update_time) "
                sql &= "VALUES (?,?,?,(SELECT CURRENT_TIMESTAMP(6)))"
                rtn = DBacs.ExecuteUpdateSql(sql, -1, en.ng_num, en.ng_num, en.b_gt_sys_id)
            End If

            sql = ""
            sql &= "UPDATE sf.b_gt "
            sql &= " SET ng_num = ng_num + ?, ok_num = picking_num - ng_num - ?, update_time= (SELECT CURRENT_TIMESTAMP(6))"
            sql &= " WHERE sys_id = ? "
            DBacs.ExecuteUpdateSql(sql, -1, en.ng_num, en.ng_num, en.b_gt_sys_id)


            DBacs.CommitTransaction()
        Catch ex As Exception
            DBacs.RollbackTransaction()
            rtn = -2
        End Try
        Return rtn
    End Function

    Public Function updateValidity(sys_id As String, procedure_no As String, b_gt_sys_id As String, ng_num As String, oper As String) As Integer
        Dim sql As String = ""
        Dim rtn As Integer
        DBacs.BeginTransaction()
        Try
            Dim integer_procedure_No As Integer = Integer.Parse(procedure_no)

            sql &= "UPDATE sf.b_gt_ng_details"
            sql &= " SET validity = ?,operator_validity = ?, update_time_validity = (SELECT CURRENT_TIMESTAMP(6)) "
            sql &= " WHERE sys_id = ? "
            rtn = DBacs.ExecuteUpdateSql(sql, -1, "0", oper, sys_id)

            sql = ""
            sql &= "UPDATE sf.b_gt_ng_procedure"
            sql &= " SET all_ng_num = all_ng_num - ?, update_time = (SELECT CURRENT_TIMESTAMP(6)) "
            sql &= " WHERE procedure_no = ? and b_gt_sys_id = ? "
            rtn = DBacs.ExecuteUpdateSql(sql, -1, ng_num, procedure_no, b_gt_sys_id)

            sql = ""
            sql &= "UPDATE sf.b_gt_ng SET all_ng_num = un_oper_ng_num - oper_ng_num - ?,"
            If integer_procedure_No > 0 Then
                sql &= " oper_ng_num = oper_ng_num - ?, "
            Else
                sql &= " un_oper_ng_num = un_oper_ng_num - ?, "
            End If
            sql &= " update_time = (SELECT CURRENT_TIMESTAMP(6)) "
            sql &= " WHERE b_gt_sys_id = ? "
            rtn = DBacs.ExecuteUpdateSql(sql, -1, ng_num, ng_num, b_gt_sys_id)

            sql = ""
            sql &= "UPDATE sf.b_gt "
            sql &= " SET ok_num = picking_num - (ng_num - ?), ng_num = ng_num - ?, update_time= (SELECT CURRENT_TIMESTAMP(6))"
            sql &= " WHERE sys_id = ? "
            DBacs.ExecuteUpdateSql(sql, -1, ng_num, ng_num, b_gt_sys_id)

            DBacs.CommitTransaction()
        Catch ex As Exception
            DBacs.RollbackTransaction()
            rtn = -2
        End Try
        Return rtn
    End Function

    Public Function getListByGtSysId(gt_sys_id As String) As DataTable
        Dim szSql As String = ""
        szSql &= " select * from sf.b_gt_ng_reason_details "
        szSql &= "where 1=1 and b_gt_sys_id = ? and validity = ?"
        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, gt_sys_id, "1")
        If IsNothing(dt) Then
            getListByGtSysId = New DataTable
        Else
            getListByGtSysId = dt
        End If
    End Function

End Class
